Designing an Employee and Department Database in MySQL

Course: MySQL Training

Topic: Designing an Employee and Department Database

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Employee and Department Database Design

Database design is the process of organizing data into tables, defining relationships between tables, selecting appropriate data types, and applying constraints to maintain accuracy and consistency. In an organization, employee and department information is commonly stored in a relational database.

An Employee and Department Database in MySQL stores details about employees, their departments, job titles, salaries, joining dates, and reporting relationships. Instead of repeating department information for every employee, the database separates department records from employee records and connects them using a foreign key.

This design makes it easier to add departments, assign employees, retrieve department-wise employee lists, calculate salary totals, and maintain consistent information.

1.1 What Is an Employee Database?

An employee database stores information about people working in an organization. It may contain employee IDs, names, email addresses, phone numbers, joining dates, salaries, job titles, and department assignments.

1.2 What Is a Department Database?

A department table stores information about organizational departments, such as department IDs, department names, office locations, and department budgets.

1.3 Why Separate Employee and Department Tables?

• Reduces unnecessary repetition of department information.
• Maintains consistency when department details change.
• Allows multiple employees to belong to the same department.
• Supports reliable joins and department-wise reports.
• Uses primary keys and foreign keys to enforce relationships.
• Makes the database easier to maintain and extend.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the requirements of an employee management database.
• Identify entities, attributes, primary keys, and foreign keys.
• Design Employee and Department tables.
• Select suitable MySQL data types.
• Apply NOT NULL, UNIQUE, DEFAULT, and CHECK constraints where appropriate.
• Establish one-to-many relationships between departments and employees.
• Insert, update, retrieve, and delete records safely.
• Write SQL queries using JOIN, GROUP BY, aggregate functions, and subqueries.
• Apply normalization principles to reduce data redundancy.
• Test and troubleshoot database design issues.

3. Understanding Database Requirements

Before creating tables, identify what information the organization needs to store and what operations users need to perform. Requirements determine the tables, columns, constraints, and relationships.

3.1 Example Business Requirements

1. Each employee must have a unique employee ID.
2. Each department must have a unique department ID.
3. Each department has a name and may have a location.
4. An employee may be assigned to a department.
5. A department can contain multiple employees.
6. Employee names, joining dates, and other required fields must be stored consistently.
7. Employee email addresses should not be duplicated when the business requires unique email addresses.
8. The system must support employee searches, department lists, and salary reports.
9. Employee records should not accidentally reference departments that do not exist.

3.2 Identifying Entities

An entity represents a real-world object about which information must be stored. In relational database design, each entity is commonly represented by a table.

4. Designing the Department Table

The Department table stores department information independently of individual employees. This prevents the same department name and location from being repeated unnecessarily in every employee record.

4.1 Recommended Department Columns

4.2 Creating the Department Table

CREATE DATABASE company_db;
USE company_db;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100),
    budget DECIMAL(12,2),
    CONSTRAINT chk_department_budget
        CHECK (budget IS NULL OR budget >= 0)
) ENGINE=InnoDB;

Explanation:

• CREATE DATABASE company_db creates a database named company_db.
• USE company_db selects the database for subsequent operations.
• department_id uniquely identifies each department.
• AUTO_INCREMENT automatically generates an ID when one is not explicitly provided.
• PRIMARY KEY prevents duplicate and NULL department identifiers.
• NOT NULL requires a department name.
• UNIQUE prevents duplicate department names.
• DECIMAL(12,2) is suitable for monetary values requiring two decimal places.
• The CHECK constraint rejects negative budgets when the constraint is enforced by the MySQL version in use.
• ENGINE=InnoDB uses the storage engine that supports transactions and foreign keys.

4.3 Example Department Records

INSERT INTO departments
    (department_name, location, budget)
VALUES
    ('IT', 'Mumbai', 500000.00),
    ('Human Resources', 'Pune', 250000.00),
    ('Finance', 'Mumbai', 350000.00),
    ('Marketing', 'Navi Mumbai', 300000.00),
    ('Sales', 'Thane', 400000.00);

Each INSERT row creates a separate department record. The database automatically generates department IDs.

5. Designing the Employee Table

The Employee table stores the details of each employee. The department_id column links an employee to a department in the Department table.

5.1 Recommended Employee Columns

5.2 Creating the Employee Table

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) UNIQUE,
    phone VARCHAR(20),
    job_title VARCHAR(100),
    salary DECIMAL(10,2),
    hire_date DATE NOT NULL,
    department_id INT,
    manager_id INT,
    CONSTRAINT chk_employee_salary
        CHECK (salary IS NULL OR salary >= 0),
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB;

Explanation:

• employee_id is the primary key for each employee.
• first_name and last_name are required fields.
• email has a UNIQUE constraint so that non-NULL email values cannot be duplicated.
• phone uses VARCHAR because phone numbers can contain plus signs, spaces, and leading zeros.
• salary uses DECIMAL for predictable monetary precision.
• hire_date stores the date the employee joined the organization.
• department_id references a department record.
• manager_id references another employee in the same table.
• The salary CHECK constraint rejects negative salaries when enforced.
• ON DELETE SET NULL preserves an employee record if its referenced department or manager is deleted, by removing that relationship.

Design note: In this example, employees may have no department or manager because both columns allow NULL. If every employee must belong to a department, make department_id NOT NULL after deciding how to handle employees awaiting assignment. If department deletion should be prohibited while employees are assigned, use ON DELETE RESTRICT instead of SET NULL.

6. Understanding Primary Keys and Foreign Keys

6.1 Primary Key

A primary key uniquely identifies every row in a table. It cannot contain duplicate values or NULL values. Each table has one primary-key definition, which may consist of one or multiple columns.

Example:

employee_id INT AUTO_INCREMENT PRIMARY KEY

This defines an automatically generated employee identifier that uniquely identifies each employee record.

6.2 Foreign Key

A foreign key connects records in one table to valid records in another table. In the employee database, employees.department_id references departments.department_id.

FOREIGN KEY (department_id)
REFERENCES departments(department_id)

A foreign-key constraint prevents an employee from referencing a department ID that does not exist, provided foreign-key checking is enabled and the operation is subject to the constraint.

MySQL's InnoDB engine supports foreign keys and uses them to help maintain referential integrity. The referenced and referencing columns must have compatible types, and the referenced key must have a suitable index. Read the official MySQL foreign-key documentation.

6.3 Difference Between Primary Key and Foreign Key

7. Understanding the Relationship Between Employees and Departments

The standard relationship between departments and employees is one-to-many. One department can have many employees, while each employee is assigned to zero or one department in this example.

7.1 Relationship Diagram

+-----------------------+
|      departments      |
+-----------------------+
| PK department_id      |
|    department_name    |
|    location           |
|    budget             |
+-----------------------+
            |
            | One department
            | can have many employees
            |
            v
+-----------------------+
|       employees       |
+-----------------------+
| PK employee_id        |
|    first_name         |
|    last_name          |
|    email              |
|    job_title          |
|    salary             |
|    hire_date          |
| FK department_id      |
| FK manager_id         |
+-----------------------+

7.2 Relationship Explanation

• One department to many employees: The department's primary key can appear in multiple employee rows.
• One employee to one department at a time: A single department_id column stores the current department assignment.
• Optional department assignment: A NULL department_id means the employee is not currently assigned to a department.
• Self-referencing manager relationship: The optional manager_id column points to another employee record.

If employees must belong to several departments simultaneously, use a junction table such as employee_departments rather than storing multiple department IDs in one column.

8. Inserting Employee Records

Once the department records exist, employee records can be inserted using valid department IDs. The following example assumes the department IDs were generated in the order shown earlier, beginning with 1.

INSERT INTO employees
    (first_name, last_name, email, phone, job_title,
     salary, hire_date, department_id, manager_id)
VALUES
    ('Aarav', 'Sharma', '[email protected]', '9876500001',
     'Software Developer', 55000.00, '2025-01-15', 1, NULL),
    ('Priya', 'Patel', '[email protected]', '9876500002',
     'HR Executive', 42000.00, '2025-02-10', 2, NULL),
    ('Rohan', 'Mehta', '[email protected]', '9876500003',
     'Accountant', 48000.00, '2024-11-20', 3, NULL),
    ('Sneha', 'Desai', '[email protected]', '9876500004',
     'Marketing Specialist', 46000.00, '2025-03-05', 4, NULL),
    ('Kabir', 'Joshi', '[email protected]', '9876500005',
     'Sales Executive', 40000.00, '2025-04-01', 5, NULL);

Important: For a reusable script, do not assume generated department IDs will always have particular values. Query the department table or use a controlled lookup to obtain the intended IDs.

8.1 Verify Inserted Records

SELECT * FROM employees;
SELECT * FROM departments;

These statements display the records in each table. In a real application, select only the columns needed rather than using SELECT * for every operation.

9. Retrieving Employee and Department Information

9.1 Display All Employees

SELECT employee_id, first_name, last_name, job_title,
       salary, department_id
FROM employees;

This query displays selected employee information without joining the department table.

9.2 Display All Departments

SELECT department_id, department_name, location, budget
FROM departments
ORDER BY department_name;

The ORDER BY clause sorts departments alphabetically by name.

9.3 Display Employees With Department Names

An INNER JOIN combines employee records with matching department records.

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.job_title,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
ORDER BY d.department_name, e.last_name;

Here, e and d are table aliases. The query displays only employees whose department assignment matches a department record.

9.4 Display All Employees Including Those Without Departments

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
ORDER BY e.employee_id;

LEFT JOIN returns all employees, even when they have no assigned department. For those employees, department columns appear as NULL.

9.5 Find Employees in a Specific Department

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.job_title
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_name = 'IT';

This query retrieves employees assigned to the IT department.

9.6 Find Employees Earning More Than a Given Salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 45000
ORDER BY salary DESC;

This query lists employees whose salary is greater than 45,000 and displays the highest salary first.

10. Department-Wise Reports and Aggregate Functions

Aggregate functions summarize multiple records. They are commonly used to calculate employee counts, salary totals, average salaries, and minimum or maximum salaries.

10.1 Count Employees in Each Department

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_name;

LEFT JOIN includes departments that have no employees. COUNT(e.employee_id) returns zero for a department without matching employee records.

10.2 Calculate Average Salary by Department

SELECT
    d.department_name,
    AVG(e.salary) AS average_salary
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

AVG ignores NULL salary values. If a department has no non-NULL salary values, its average will be NULL.

10.3 Calculate Total Salary by Department

SELECT
    d.department_name,
    COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

SUM calculates the total of non-NULL salaries. COALESCE displays zero when the result is NULL. Whether zero is the best reporting value depends on the business meaning of an empty department or missing salary data.

10.4 Find Departments With More Than Two Employees

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
HAVING COUNT(e.employee_id) > 2;

WHERE filters individual rows before grouping. HAVING filters groups after aggregate calculations.

10.5 Find the Highest-Paid Employee

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

This query returns every employee tied for the highest non-NULL salary.

11. Updating Employee and Department Records

UPDATE statements modify existing records. A WHERE clause should generally be included to ensure that only intended records are changed.

11.1 Update an Employee's Salary

UPDATE employees
SET salary = 60000.00
WHERE employee_id = 1;

This changes the salary of the employee whose ID is 1. Verify the employee identifier before executing an update in production.

11.2 Transfer an Employee to Another Department

UPDATE employees
SET department_id = 3
WHERE employee_id = 1;

The new department ID must exist in the Department table. The foreign-key constraint rejects an invalid department reference.

11.3 Update Department Location

UPDATE departments
SET location = 'Pune'
WHERE department_id = 1;

This changes the location stored for the specified department. Employee records do not need to be updated because they reference the department by ID.

11.4 Safer Update Workflow

1. Use SELECT to identify the target record.
2. Confirm the primary key and the intended values.
3. Use a WHERE clause in the UPDATE statement.
4. Check the affected-row count.
5. Run a SELECT query to verify the result.
6. Use a transaction where appropriate for multi-step operations.

12. Deleting Employee and Department Records

Deleting data must be handled carefully because a deletion can affect reports, audit requirements, and related records.

12.1 Delete an Employee

DELETE FROM employees
WHERE employee_id = 5;

This deletes the employee with ID 5, provided the employee is not referenced by another row through a restrictive foreign-key relationship. In the example schema, manager references use ON DELETE SET NULL.

12.2 Delete a Department

DELETE FROM departments
WHERE department_id = 5;

In the example schema, deleting a department sets the department ID of its assigned employees to NULL. This preserves the employee records but removes their department assignments.

In many organizations, deleting a department may be undesirable for historical reporting. An alternative is to add an is_active or status column and mark the department inactive instead of deleting it.

12.3 Referential Actions

Choose the action according to business rules, not merely convenience. CASCADE can delete many dependent records, so it should be used only when that behavior is intentional.

13. Normalization in Employee and Department Database Design

Normalization organizes data to reduce unnecessary repetition and prevent update, insertion, and deletion anomalies. Employee and department information is a useful example of why related data should be stored in separate tables.

13.1 Unnormalized Design

Imagine storing employee and department information in one table:

In this design, the IT department's name and location are repeated for every IT employee. If the location changes, several rows may need updating.

13.2 First Normal Form (1NF)

First Normal Form requires each column to contain a single value for each row, rather than storing multiple department IDs or phone numbers as a comma-separated list in one field.

For example, avoid storing department_ids = '1,2,3' in an employee row. Use a proper relationship or junction table when multiple assignments are needed.

13.3 Second Normal Form (2NF)

Second Normal Form requires a table to be in 1NF and non-key attributes to depend on the whole candidate key. It is particularly relevant to tables with composite keys.

For the simple Employee table, employee-specific attributes depend on the employee ID. Department-specific attributes belong in the Department table rather than being repeated in employee rows.

13.4 Third Normal Form (3NF)

Third Normal Form aims to eliminate inappropriate transitive dependencies between non-key attributes. In this design, department name and department location depend on the department identifier, not directly on the employee identifier.

Separating the Department table from the Employee table supports this design and reduces unnecessary duplication.

13.5 Benefits of Normalization

• Reduces repeated data.
• Improves consistency when department information changes.
• Helps prevent insertion and deletion anomalies.
• Supports clearer relationships between business entities.
• Makes constraints and data ownership easier to define.

14. Indexing and Performance Optimization

Indexes help MySQL locate rows efficiently. Primary keys and UNIQUE constraints create supporting indexes, and MySQL requires suitable indexes for foreign-key relationships. Additional indexes should be chosen according to actual query patterns.

14.1 Indexing Department Assignments

CREATE INDEX idx_employees_department
ON employees(department_id);

This index can help queries that filter or join employees by department. MySQL may already have created an index to support the foreign key, so inspect existing indexes before creating another one.

14.2 Indexing Hire Dates

CREATE INDEX idx_employees_hire_date
ON employees(hire_date);

This index may help queries that frequently filter or sort employees by joining date. It is unnecessary if the workload does not benefit from it.

14.3 Inspecting Existing Indexes

SHOW INDEX FROM employees;
SHOW INDEX FROM departments;

These statements display the indexes defined for the tables.

14.4 Using EXPLAIN

EXPLAIN
SELECT
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_name = 'IT';

Review the execution plan to understand the access methods and selected indexes. Do not add indexes solely because a column appears in a query; validate the improvement with realistic data and representative workload measurements.

15. Adding Constraints to Existing Tables

When a database already exists, constraints can be added with ALTER TABLE. Before applying a constraint, inspect and correct existing records that violate the proposed rule.

15.1 Add a Unique Email Constraint

ALTER TABLE employees
ADD CONSTRAINT uq_employees_email UNIQUE (email);

If the column already has a UNIQUE constraint, adding another one is unnecessary. Existing duplicate non-NULL email values can cause the statement to fail.

15.2 Add a Foreign Key

ALTER TABLE employees
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

This example applies when the foreign key has not already been created. Before adding it, confirm that every non-NULL employee department ID references an existing department and that the column definitions are compatible.

15.3 Find Employees With Invalid Department References

SELECT e.employee_id, e.department_id
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE e.department_id IS NOT NULL
  AND d.department_id IS NULL;

In a correctly enforced foreign-key relationship, this query should return no invalid references. It is useful when examining imported legacy data or data created while constraints were not enforced.

16. Designing a Many-to-Many Employee Assignment

The basic design assumes that an employee belongs to one department at a time. Some organizations require employees to work across several departments or projects. This requires a different relationship model.

16.1 Creating a Junction Table

CREATE TABLE employee_departments (
    employee_id INT NOT NULL,
    department_id INT NOT NULL,
    assigned_date DATE NOT NULL,
    PRIMARY KEY (employee_id, department_id),
    CONSTRAINT fk_ed_employee
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_ed_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

The junction table connects employees and departments. Its composite primary key prevents the same employee-department pair from being inserted more than once.

The CASCADE rules in this example delete assignment records when the associated employee or department is deleted. They do not directly delete the employee or department because those are the referenced parent records.

16.2 Insert an Employee Assignment

INSERT INTO employee_departments
    (employee_id, department_id, assigned_date)
VALUES
    (1, 2, '2026-01-10');

This creates an additional department assignment for employee 1, provided the employee and department exist.

16.3 Query Multiple Department Assignments

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    ed.assigned_date
FROM employee_departments ed
JOIN employees e
    ON ed.employee_id = e.employee_id
JOIN departments d
    ON ed.department_id = d.department_id
ORDER BY e.employee_id, d.department_name;

Use the junction-table model when the business requirement genuinely permits multiple department assignments. Avoid maintaining two competing sources of truth for the same relationship.

17. Employee Manager Relationships

Organizations often have reporting hierarchies. A manager is also an employee, so a self-referencing foreign key can represent this relationship.

17.1 Assign a Manager

UPDATE employees
SET manager_id = 1
WHERE employee_id = 2;

This assigns employee 1 as the manager of employee 2, assuming both records exist and the assignment satisfies business rules.

17.2 Display Employees and Their Managers

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    CONCAT(m.first_name, ' ', m.last_name) AS manager_name
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

The table is joined to itself. The alias e represents the employee, while m represents the manager. LEFT JOIN keeps employees who have no manager.

17.3 Manager Relationship Rules

• An employee should not normally be their own manager.
• Reporting relationships should not form cycles.
• Top-level managers may have a NULL manager ID.
• Deleting a manager should follow the organization's policy for reassignment or removal of manager references.

The foreign key ensures that a non-NULL manager ID references an existing employee, but it does not by itself prevent self-management or longer reporting cycles. Additional application validation or suitable database logic is needed for those rules.

18. Using Transactions for Multiple Database Changes

Transactions help ensure that related database changes succeed together or are rolled back. They are particularly useful when creating an employee and performing other required database updates in one logical operation.

18.1 Example Transaction

START TRANSACTION;

INSERT INTO employees
    (first_name, last_name, email, job_title,
     salary, hire_date, department_id)
VALUES
    ('Anaya', 'Kulkarni', '[email protected]',
     'Data Analyst', 52000.00, '2026-02-01', 1);

COMMIT;

In this example, the transaction commits the employee insertion. In an application, error handling should issue ROLLBACK if the required operation fails before COMMIT.

18.2 Transaction Example With Error Handling

START TRANSACTION;

UPDATE employees
SET salary = salary + 2000
WHERE department_id = 1;

-- Verify the affected rows and business rules.
-- If the operation is valid:
COMMIT;

-- If the operation cannot be completed:
-- ROLLBACK;

The comments illustrate the decision process; COMMIT and ROLLBACK are alternative outcomes, not commands to execute sequentially in the same successful transaction. Applications should decide which outcome to use based on the actual result.

19. Common Database Design Mistakes

20. Troubleshooting Employee and Department Database Issues

20.1 Foreign-Key Creation Fails

Possible causes: Column type mismatch, missing referenced key, incompatible storage engines, existing invalid references, or an incorrectly defined constraint.

Solutions:

• Compare the data types and signedness of the referencing and referenced columns.
• Verify that the referenced column has a suitable index.
• Confirm that both tables use a compatible storage engine such as InnoDB.
• Find and resolve existing invalid department references.
• Inspect the exact MySQL error message before changing the schema.

20.2 Duplicate Email Error

Cause: The email value already exists in a column with a UNIQUE constraint.

Solution: Search for the existing email, verify whether the operation is a duplicate request, and update the correct record or use a valid unique address. Do not remove the constraint simply to bypass the error.

20.3 Department Name Is Missing

Possible cause: The employee has no department assignment, or an INNER JOIN excludes the employee because there is no matching department.

Solution: Use LEFT JOIN when unassigned employees should appear in the results and inspect whether department_id is NULL.

20.4 Unexpectedly High Salary Totals

Possible cause: A query joins multiple one-to-many relationships and duplicates employee rows before calculating SUM.

Solution: Check join cardinality, aggregate each relationship at the correct level, and compare the result with a direct salary total from the Employee table.

20.5 Employee Cannot Be Deleted

Possible cause: Another row references the employee, such as an employee whose manager ID points to that employee, and the relevant constraint prevents deletion.

Solution: Inspect dependent rows, reassign manager relationships where appropriate, and follow the intended foreign-key deletion policy.

21. Practical Mini Project: Company Employee Management Database

Project objective: Build a MySQL database that stores department information, employee details, reporting relationships, and department-wise salary reports.

21.1 Project Requirements

• Create a database named company_db.
• Create the Department and Employee tables.
• Define primary keys and foreign keys.
• Insert at least five departments and ten employees.
• Assign employees to departments.
• Assign managers to selected employees.
• Retrieve employee details with department names.
• Generate department-wise employee counts and salary totals.
• Update an employee's salary and department assignment.
• Test duplicate email and invalid department references.

21.2 Suggested SQL Tasks

1. Display all employees in the IT department.
2. Find employees whose salary is greater than 50,000.
3. Count employees in each department, including empty departments.
4. Calculate average salary by department.
5. Display employees who do not have a department assignment.
6. Display each employee with their manager's name.
7. Find the department with the highest total salary cost.
8. List employees hired after a specified date.
9. Update the location of a department.
10. Test how the selected foreign-key rules behave when deleting a department.

21.3 Sample Query: Department With Highest Total Salary

SELECT
    d.department_name,
    COALESCE(SUM(e.salary), 0) AS total_salary
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY total_salary DESC
LIMIT 1;

This query returns one department with the highest calculated salary total. If multiple departments are tied, LIMIT 1 returns only one of them. To return all tied departments, use a query that compares each department's total with the maximum total.

21.4 Project Validation Checklist

• Each employee has a unique employee ID.
• Each department has a unique department ID.
• Required employee and department fields reject NULL values.
• Duplicate email addresses are rejected when an email is supplied more than once.
• Invalid department references are rejected.
• Salary and budget constraints work as intended.
• JOIN queries return the expected employees and departments.
• Aggregate results are not inflated by duplicate join rows.
• Delete and update actions follow documented business rules.
• Backup and recovery procedures have been tested.

22. Interview Questions and Answers

Q1. What is database design?

Database design is the process of defining tables, columns, relationships, data types, keys, and constraints to represent information accurately and efficiently.

Q2. Why should Employee and Department information be stored in separate tables?

Separating the tables reduces data duplication, improves consistency, and allows employees to reference a department through a foreign key.

Q3. What is a primary key?

A primary key uniquely identifies each row in a table and cannot contain NULL or duplicate key values.

Q4. What is a foreign key?

A foreign key establishes a relationship between a referencing column and a key in another table or the same table. It helps maintain referential integrity.

Q5. What relationship exists between departments and employees?

Typically, it is a one-to-many relationship: one department can contain multiple employees, while an employee belongs to one department at a time in the basic design.

Q6. Why is department_id stored in the Employee table?

It links an employee to the corresponding department without repeating the full department information in every employee record.

Q7. Why use VARCHAR for phone numbers?

Phone numbers are identifiers rather than quantities. VARCHAR preserves leading zeros and supports symbols such as plus signs.

Q8. Why use DECIMAL for salary?

DECIMAL stores fixed-precision numeric values and is generally more appropriate than floating-point types for monetary amounts.

Q9. What is normalization?

Normalization organizes related data into appropriate tables to reduce unnecessary repetition and prevent insertion, update, and deletion anomalies.

Q10. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns rows with matching records on both sides. LEFT JOIN returns all rows from the left table, including those without a matching row on the right.

Q11. What does ON DELETE SET NULL do?

It sets the referencing foreign-key column to NULL when the referenced parent row is deleted, provided the column allows NULL.

Q12. What is a self-referencing foreign key?

It is a foreign key that references another row in the same table, such as an employee's manager ID referencing another employee.

Q13. What is a junction table?

A junction table connects two tables in a many-to-many relationship. For example, employee_departments can connect employees with multiple departments.

Q14. How do you find the number of employees in each department?

Join departments to employees, group by department, and use COUNT on the employee primary key. A LEFT JOIN includes departments with no employees.

Q15. How can you prevent duplicate email addresses?

Apply a UNIQUE constraint to the email column when unique email addresses are a business requirement.

Q16. What is the purpose of an index?

An index helps MySQL locate matching rows more efficiently. It requires storage and can increase the cost of data modifications.

Q17. What happens if an employee references a department that does not exist?

With foreign-key enforcement enabled, MySQL rejects the operation because the department reference violates referential integrity.

Q18. What is the difference between hard deletion and soft deletion?

Hard deletion removes a row from the table. Soft deletion retains the row but marks it inactive or deleted using a status field or timestamp.

23. Quick Revision Checklist

• Understand the requirements before designing the database.
• Identify the Employee and Department entities.
• Use suitable data types for names, email, phone, dates, and salary.
• Define primary keys for both tables.
• Use a foreign key to link employees with departments.
• Choose deliberate ON DELETE and ON UPDATE rules.
• Understand one-to-many and many-to-many relationships.
• Use JOINs to retrieve related employee and department details.
• Use GROUP BY and aggregate functions for departmental reports.
• Apply normalization to reduce unnecessary duplication.
• Use indexes based on actual query requirements.
• Validate constraints, test SQL operations, and protect important data.

24. Conclusion

Designing an Employee and Department Database is a practical exercise in relational database modeling. By separating department and employee information, selecting suitable data types, defining primary and foreign keys, and applying meaningful constraints, a MySQL database can maintain reliable organizational information.

Queries using JOIN, GROUP BY, aggregate functions, and subqueries make it possible to retrieve employee details and create useful reports. Normalization, appropriate indexing, transaction management, and a clear deletion policy further improve reliability and maintainability.

Practice the complete design by creating the tables, inserting sample data, testing constraints, writing reports, and troubleshooting errors in a development database before applying similar changes in a production environment.

25. Additional Learning Resources

• Official MySQL Documentation
• MySQL CREATE TABLE Statement
• MySQL Foreign-Key Constraints
• MySQL Optimization and Indexes

Enroll in MySQL Training

Build practical database skills by learning MySQL queries, relational database design, table relationships, constraints, joins, indexing, and database management.

Course Link: Explore MySQL Training at JustAcademy

Demo Link: Register for a MySQL Training Demo